import sqlite3
import os

conn = sqlite3.connect(database=':memory:')
cursor = conn.cursor()

# 创建表， create table
cursor.execute(
    '''
    CREATE TABLE Team_1(
        id INT PRIMARY KEY NOT NULL,
        name TEXT NOT NULL,
        gender TEXT NOT NULL,
        height INT,
        weight REAL
    );
    '''
)

# 表中插入数据
cursor.execute(
    '''
    INSERT INTO Team_1(id, name, gender, height, weight)
    VALUES(1, 'Allen', 'man', 170, 60.5)
    '''
)

cursor.execute(
    '''
    INSERT INTO Team_1(id, name, gender, height, weight)
    VALUES(2, 'Marry', 'woman', 165, 55)
    '''
)

cursor.execute(
    '''
    INSERT INTO Team_1(id, name, gender, height, weight)
    VALUES(3, 'Tom', 'man', 173, 63.3)
    '''
)

cursor.execute(
    '''
    INSERT INTO Team_1(id, name, gender, height, weight)
    VALUES(4, 'Allice', 'woman', 159, 50.4)
    '''
)

cursor.execute(
    '''
    INSERT INTO Team_1(id, name, gender, height, weight)
    VALUES(5, 'Bob', 'man', 178, 68.6)
    '''
)

# -----------------Select--------------------------------------
# 输出table 所有列
data = cursor.execute('SELECT * FROM Team_1;')
for row in data:
    print(row)

# 输出指定列
data = cursor.execute('SELECT name FROM Team_1;')
for row in data:
    print(row)

# 检索 + 条件过滤
data = cursor.execute("SELECT * FROM Team_1 WHERE gender='man';")
for row in data:
    print(row)

# AND
data = cursor.execute("SELECT * FROM Team_1 WHERE (gender='man') AND (weight<65)")
for row in data:
    print(row)

# OR
data = cursor.execute("SELECT * FROM Team_1 WHERE (height>=170) OR (weight<=65)")
for row in data:
    print(row)